
/**
	Author		: Rohan
	Date		: July 1st 2004
	Description	: This proc is used to save or update the sales order details in CT database
			  while migrating the sales orders from Epicore to CT. For updating the sales order
			  details this proc internally calls the sp_change_sales_order_proc procedure.
			
*/
ALTER  proc sp_save_epicore_sales_order

@SALES_ORDER_NO       VARCHAR(50),
@CUST_NUMBER          VARCHAR(50),
@CUST_PO_NO           VARCHAR(50),
@CONTACT_PERSON       VARCHAR(50),
@CONTACT_PERSON_PH_NO VARCHAR(50),
@SALES_PERSON         VARCHAR(50),
@SHIP_METHOD          VARCHAR(50),
@SHIPPING_INSTR       TEXT,
@SHIPPING_TERMS       VARCHAR(50),
@SALES_TAX_TYPE       VARCHAR(50),
@SALES_ORDER_DATE     DATETIME,
@EPICORE_SHIP_CODE    VARCHAR(8),
@EPICORE_SO_STATUS    CHAR(1),
@SUB_TOTAL            FLOAT,
@SALES_TAX_AMT        FLOAT,
@FREIGHT_AMT          FLOAT,
@MISC_AMT             FLOAT,
@USER_ID              VARCHAR(50),
@USER_NAME            VARCHAR(50),
@INSERT_CHANGE_SO     INT, 
@SALES_ORDER_ID       INT OUT

AS
BEGIN

--DECLARATIONS
DECLARE @CUST_ID        INT
DECLARE @SHIP_ACCT      VARCHAR(50)
DECLARE @COMMENTS       VARCHAR(255)
DECLARE @END_USER       VARCHAR(50)
DECLARE @REGIONAL_REP   VARCHAR(50)
DECLARE @DELIVERY_TERMS VARCHAR(50)
DECLARE @MISC_NAME      VARCHAR(50)
DECLARE @CUST_NAME      VARCHAR(50)
DECLARE @PHONE          VARCHAR(50)
DECLARE @FAX            VARCHAR(50)
DECLARE @EMAIL          VARCHAR(50)
DECLARE @CELL_NUMBER    VARCHAR(50)
DECLARE @ADDRESS        VARCHAR(50)
DECLARE @BILLING_CONTACT_PERSON VARCHAR(50)
DECLARE @SHIPPING_CONTACT_PERSON VARCHAR(50)
DECLARE @STATUS_FLAG         INT
DECLARE @CANCELLED           INT
DECLARE @SHIPPED             INT
DECLARE @PENDING             INT
DECLARE @BILLING_ADDR_ID     INT
DECLARE @SHIPPING_ADDR_ID    INT
DECLARE @SALES_CHANGE_NUMBER INT

--INITIALISATIONS
SET @SHIP_ACCT = ''

IF( @CONTACT_PERSON IS NULL )
BEGIN
	SET @CONTACT_PERSON = ''
END

IF( @CONTACT_PERSON_PH_NO IS NULL )
BEGIN
	SET @CONTACT_PERSON_PH_NO = ''
END

IF( @CUST_PO_NO IS NULL )
BEGIN
	SET @CUST_PO_NO = ''
END

IF( @CUST_PO_NO = '' )
BEGIN
	SET @CUST_PO_NO = @SALES_ORDER_NO
END

IF( @SALES_PERSON IS NULL )
BEGIN
	SET @SALES_PERSON = ''
END

--Inserting the Shipping Method, Payment Terms, Sales Tax Type which are not existing in there respective master tables
EXEC sp_ct_sls_insert_sales_order_related_if_not_existing @CUST_ID, @SHIP_METHOD, @SHIP_ACCT, @SALES_TAX_TYPE, @SALES_PERSON, @SHIPPING_TERMS, @CONTACT_PERSON, @CONTACT_PERSON_PH_NO 

--Retreiving the shipping account corresponding to the given Shipping Method
SELECT @SHIP_ACCT = SHIPPING_ACCOUNT FROM SHIPPING_METHODS_TABLE NOLOCK WHERE SHIPPING_METHOD = @SHIP_METHOD AND SM_CUSTOMER_ID = @CUST_ID

--Retreiving Customer details
SELECT @CUST_ID = CUSTOMER_ID, @CUST_NAME = C_NAME, @SHIPPING_ADDR_ID = C_Default_Shipping_Address_ID, @BILLING_ADDR_ID = C_Default_Billing_Address_ID FROM CUSTOMER NOLOCK WHERE C_NUMBER = @CUST_NUMBER

--Retreiving the Shipping Address ID bases on the Epicore Ship CodeEPICORE SHIP CODE INCASE IT IS DIFFERENT FROM THE DEFAULT SHIPPING ADDRESS 
SELECT @SHIPPING_ADDR_ID = Address_ID FROM Address NOLOCK WHERE ( Address_ID in ( SELECT Address_Id FROM Customer_Shipping_Address_Table WHERE customer_id = @CUST_ID ) or Address_ID = ( SELECT C_Default_Shipping_Address_ID FROM customer WHERE customer_id = @CUST_ID ) or Address_ID = ( SELECT C_Default_Billing_Address_ID FROM customer WHERE customer_id = @CUST_ID ) ) and Epicore_Ship_Code = @EPICORE_SHIP_CODE

--SETTING THE PROPERTIES OF THE SALES ORDER LIKE CANCELLED, SHIPPED, PENDING BASED ON THE EPICORE SO STATUS
SET @SHIPPED   = 1

--If the Epicore Status is either Void or Cancelled then set the cancelled property as true
IF( @EPICORE_SO_STATUS = 'V' OR @EPICORE_SO_STATUS = 'X' )
BEGIN
	SET @CANCELLED = 0
END
ELSE
BEGIN
	SET @CANCELLED = 1
END

--If the Epicore Status is User Hold then set the Sales Order on Hold
IF( @EPICORE_SO_STATUS = 'A' )
BEGIN
	SET @PENDING = 0
END
ELSE
BEGIN
	SET @PENDING = 1
END

--Checking is the sales order exists
SELECT @SALES_ORDER_ID = SALESORDER_ID FROM SALES_ORDER NOLOCK WHERE SALESORDER_NO = @SALES_ORDER_NO

IF( @@ROWCOUNT = 0 )
BEGIN
	--Inserting the Sales Order details
	INSERT INTO Sales_Order( Customer_ID, SO_Customer_PO_Number, SO_Contact_Person, SO_Sales_Person, SO_Shipping_Method, SO_Shipping_Account, SO_Billing_Address_ID, SO_Shipping_Address_ID, SO_Comments, SO_Shipping_Instructions, SO_EndUser, SO_Status, SO_Booking_date, SO_Is_Sample, SO_Is_Cancel, SO_Is_Pending, SO_Is_Shipped, SO_Entered_By_ID, SO_Entered_By_Name, SO_Sales_Tax_Type, SO_Sub_Total, SO_Sales_Tax, SO_Frieght_Tax, SO_Misc,SalesOrder_No,Regional_Rep, Shipping_Terms, Delivery_terms, SO_Misc_Name, timestamp, SO_Billing_Contact_Person, SO_Shipping_Contact_Person) 
        	         VALUES( @CUST_ID, @CUST_PO_NO, @CONTACT_PERSON, @SALES_PERSON, @SHIP_METHOD, @SHIP_ACCT, @BILLING_ADDR_ID, @SHIPPING_ADDR_ID, '', @SHIPPING_INSTR, '', 'PENDING', @SALES_ORDER_DATE, 1, @CANCELLED, @PENDING, @SHIPPED, @USER_ID, @USER_NAME, @SALES_TAX_TYPE, @SUB_TOTAL, @SALES_TAX_AMT, @FREIGHT_AMT, @MISC_AMT, @SALES_ORDER_NO, '', @SHIPPING_TERMS, '', '', CURRENT_TIMESTAMP, '', '' )
	
	--Retreiving the new sales order ID
	SET @SALES_ORDER_ID = @@IDENTITY
	
	--Inserting the Sales Order in the Change Sales Order table with the Change Number as 0
	INSERT INTO Change_Sales_Order( SalesOrder_ID, ChangeNo, Customer_ID, CSO_Customer_PO_Number, CSO_Contact_Person, CSO_Sales_Person, CSO_Shipping_Method, CSO_Shipping_Account, CSO_Billing_Address_ID, CSO_Shipping_Address_ID, CSO_Comments, CSO_Shipping_Instructions, CSO_EndUser, CSO_Status, CSO_Booking_date, CSO_Is_Sample, CSO_Is_Cancel, CSO_Is_Pending, CSO_Is_Shipped, CSO_Entered_By_ID, CSO_Entered_By_Name, CSO_Sales_Tax_Type, CSO_Sub_Total, CSO_Sales_Tax, CSO_Frieght_Tax, CSO_Misc,Regional_Rep, Shipping_Terms, Delivery_terms, CSO_Misc_Name, timestamp) 
        	                VALUES( @SALES_ORDER_ID, 0, @CUST_ID, @CUST_PO_NO, @CONTACT_PERSON, @SALES_PERSON, @SHIP_METHOD, @SHIP_ACCT, @BILLING_ADDR_ID, @SHIPPING_ADDR_ID, '', @SHIPPING_INSTR, '', 'PENDING', @SALES_ORDER_DATE, 1, @CANCELLED, @PENDING, @SHIPPED, @USER_ID, @USER_NAME, @SALES_TAX_TYPE, @SUB_TOTAL, @SALES_TAX_AMT, @FREIGHT_AMT, @MISC_AMT, '', @SHIPPING_TERMS, '', '', CURRENT_TIMESTAMP )
		
			
	--Updating Contact Person details
	IF( UPPER( RTRIM( @CONTACT_PERSON ) ) = UPPER( RTRIM( @CUST_NAME ) ) ) 
	BEGIN
		INSERT INTO SALES_ORDER_ADDRESS( SALESORDER_ID, PHONE_NUMBER ) VALUES( @SALES_ORDER_ID, @CONTACT_PERSON_PH_NO )
	END
	ELSE
	BEGIN
		INSERT INTO SALES_ORDER_ADDRESS( SALESORDER_ID, PHONE_NUMBER, FAX_NUMBER, EMAIL ) SELECT @SALES_ORDER_ID, PHONE_NUMBER, FAX_NUMBER, EMAIL FROM ADDRESS WHERE ADDRESS_ID = @BILLING_ADDR_ID
	END	

END --End of condition for inserting the sales order
ELSE
BEGIN
	--Updating the sales order details

	--Retreiving Sales Order details
	SELECT  @BILLING_ADDR_ID         = SO_Billing_Address_ID,
		@SHIPPED                 = SO_Is_Shipped,
		@COMMENTS                = SO_Comments, 
		@END_USER                = SO_EndUser,
		@MISC_NAME               = SO_Misc_Name,
		@REGIONAL_REP            = Regional_Rep, 
		@DELIVERY_TERMS          = Delivery_Terms, 
        	@BILLING_CONTACT_PERSON  = SO_Billing_Contact_Person , 
		@SHIPPING_CONTACT_PERSON = SO_Shipping_Contact_Person  FROM Sales_Order NOLOCK WHERE SalesOrder_ID = @SALES_ORDER_ID

	SELECT  @PHONE = Phone_Number ,@FAX = Fax_Number , @EMAIL = Email , @CELL_NUMBER = Cell_Number FROM Address NOLOCK WHERE Address_Id = @BILLING_ADDR_ID
	SET @ADDRESS = '' --Not sure from where to get the address

	EXEC sp_change_sales_order_proc @CUST_PO_NO, @CONTACT_PERSON, 
					@SALES_PERSON, @SHIP_METHOD, 
					@SHIP_ACCT, @SHIPPING_ADDR_ID, 
					@COMMENTS, @SHIPPING_INSTR, 
					@END_USER, @SALES_TAX_TYPE, 
					@SUB_TOTAL, @SALES_TAX_AMT, 
					@FREIGHT_AMT, @MISC_AMT, 
					@SALES_ORDER_NO, @REGIONAL_REP, 
					@SHIPPING_TERMS, @DELIVERY_TERMS, 
					@SALES_ORDER_DATE, @SALES_ORDER_ID, 
					'', @PHONE, 
					@FAX, @EMAIL, 
					@CELL_NUMBER, @ADDRESS, 
					@STATUS_FLAG OUT, @MISC_NAME, 
					@BILLING_ADDR_ID, @BILLING_CONTACT_PERSON, @SHIPPING_CONTACT_PERSON

	
	/*
	  This value is true whenever the amount is modified in the sales order in epicore which indicated
	  that the qty ot amt of one of the line items is changed. When ever this value true change number
          is incremented and a record  has to be inserted in the change sales order proc with that change number
	*/	
	IF( @INSERT_CHANGE_SO = 1 )
	BEGIN
		select @SALES_CHANGE_NUMBER =  isnull( max( ChangeNo ), 0 ) from Change_Sales_Order where SalesOrder_ID = @SALES_ORDER_ID
		set @SALES_CHANGE_NUMBER = @SALES_CHANGE_NUMBER + 1

		INSERT INTO Change_Sales_Order(SalesOrder_ID,ChangeNo,Customer_ID,CSO_Customer_PO_Number,CSO_Contact_Person,
		CSO_Sales_Person,CSO_Shipping_Method,CSO_Shipping_Account,CSO_Billing_Address_ID,CSO_Shipping_Address_ID,
		CSO_Comments,CSO_Shipping_Instructions,CSO_EndUser,CSO_Status,CSO_Booking_Date,CSO_Is_Sample,CSO_Is_Cancel,
		CSO_Is_Pending,CSO_Is_Shipped,CSO_Entered_By_ID,CSO_Entered_By_Name,CSO_Sales_Tax_Type,CSO_Sub_Total,
		CSO_Sales_Tax,CSO_Frieght_Tax,CSO_Misc,Alarm_ID,Timestamp,Regional_Rep,Shipping_Terms,Delivery_Terms,
		CSO_Misc_Name,CSO_Show_Pro_Forma_Invoice,CSO_Reason)
	
		(SELECT SalesOrder_ID,@SALES_CHANGE_NUMBER,Customer_ID,SO_Customer_PO_Number,SO_Contact_Person,
		SO_Sales_Person,SO_Shipping_Method,SO_Shipping_Account,SO_Billing_Address_ID,SO_Shipping_Address_ID,
		SO_Comments,SO_Shipping_Instructions,SO_EndUser,SO_Status,SO_Booking_Date,SO_Is_Sample,SO_Is_Cancel,
		SO_Is_Pending,SO_Is_Shipped,SO_Entered_By_ID,SO_Entered_By_Name,SO_Sales_Tax_Type,SO_Sub_Total,
		SO_Sales_Tax,SO_Frieght_Tax,SO_Misc,Alarm_ID,Timestamp,Regional_Rep,Shipping_Terms,Delivery_Terms,
		SO_Misc_Name,SO_Show_Pro_Forma_Invoice,''
		FROM Sales_Order WHERE SalesOrder_id = @SALES_ORDER_ID)
	END
END --End of condition for updating the sales order


--If there is User Hold on the sales order in Epicore then set the financial hold else remove all the holds of all the line item of this sales order
IF( ( @SALES_ORDER_ID IS NOT NULL ) AND @EPICORE_SO_STATUS = 'A' )
BEGIN
	exec sp_ct_remove_sales_order_holds @SALES_ORDER_ID, 1, 1, 1, 0
END
ELSE
BEGIN
	exec sp_ct_remove_sales_order_holds @SALES_ORDER_ID, 1, 1, 1, 1
END

END --End of proc


